go

exec dbo.p_drop_proc_if_exists 'meta_add_unit';

go

--Function of adding a business unit
create procedure meta_add_unit(
    --Unit id
    @a_unit_id binary(16),
    --Unit name
    @a_unit_name nvarchar(26))
with execute as owner
as
begin
	set nocount on;
	declare
    --Unit id str
    @a_unit_id_str varchar(32);
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    
    --Searching for the unit with the same id
    if exists(select 1 from meta_units where unit_id = @a_unit_id)
    begin
        raiserror('A unit with the specified id was already added (unit_id = %s) (#error_code = 20031)',
			16, 1, @a_unit_id_str);
		return;
    end;
    
    --Searching for the unit with the same name
    if exists(select 1 from meta_units where unit_name = @a_unit_name)
    begin
        raiserror('A unit with the specified name was already added (unit_name = %s) (#error_code = 20017)',
			16, 1, @a_unit_name);
		return;
    end;
    
    set xact_abort on;
    begin tran;

    --Updating the metadata
    insert into meta_units(unit_id, unit_name)
    values(@a_unit_id, @a_unit_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_unit_exists';

go

--Function of checking unit existence
create procedure p_check_unit_exists(
    --Unit id
    @a_unit_id binary(16),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
    --Unit id str
    @a_unit_id_str varchar(32);
    
    --Searching for the unit
    if not exists(select 1 from meta_units where unit_id = @a_unit_id)
    begin
		set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
		
        raiserror('A unit with the specified id was not found (unit_id = %s) (#error_code = 20032)',
        16, 1, @a_unit_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_unit';

go

--Function of deleting a business unit
create procedure meta_delete_unit(
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Result
	@a_result numeric(1);
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;
    
    --Getting affected users
    select distinct user_name into #a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    --References to and from unit will be deleted on cascade
    
    --Deleting the unit
    delete from meta_units
    where unit_id = @a_unit_id;
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Refreshing users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;    
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_unit_parents';

go

--Function of getting unit parents
create function p_get_unit_parents(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
	with cte_unit_parents(unit_id) as
	(
	    --The target unit
	    select @a_unit_id
	    union
	    --The parent units
		select parent_unit_id from meta_unit_parents
		where unit_id = @a_unit_id
		union all
		select e.parent_unit_id
		from meta_unit_parents as e
			inner join cte_unit_parents as d
			on e.unit_id = d.unit_id		
	)
	select distinct unit_id
	from cte_unit_parents
);

go

exec dbo.p_drop_func_if_exists 'p_get_unit_children';

go

--Function of getting unit children
create function p_get_unit_children(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
	with cte_unit_parents(unit_id) as
	(
	    --The target unit
	    select @a_unit_id
	    union
	    --The child units
		select unit_id from meta_unit_parents
		where parent_unit_id = @a_unit_id
		union all
		select e.unit_id
		from meta_unit_parents as e
			inner join cte_unit_parents as d
			on e.parent_unit_id = d.unit_id		
	)
	select distinct unit_id
	from cte_unit_parents
);

go

exec dbo.p_drop_func_if_exists 'p_get_unit_parents_children';

go

--Function of getting unit children and parents
create function p_get_unit_parents_children(
    --Unit id
    @a_unit_id binary(16))
returns table as
return
( 
	with cte_unit_parents(unit_id) as
	(
	    --The child units
		select unit_id from p_get_unit_children(@a_unit_id)
        union
        --The unit parents
		select unit_id from p_get_unit_parents(@a_unit_id)
	)
	select distinct unit_id
	from cte_unit_parents
);

go

exec dbo.p_drop_proc_if_exists 'meta_add_unit_parent';

go

--Function of adding unit parent
create procedure meta_add_unit_parent(
    --Unit id
    @a_unit_id binary(16),
    --Parent unit id
    @a_parent_unit_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
    --Result
    @a_result numeric(1),
    --Unit id str
    @a_unit_id_str varchar(32),
    --Parent unit id str
    @a_parent_unit_id_str varchar(32);
    --Unit id list
    declare @a_unit_id_list table (unit_id binary(16));
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_parent_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    set @a_parent_unit_id_str = dbo.p_bin_to_str(@a_parent_unit_id);
    
    --Searching for the same unit parent
    if exists(select 1 from meta_unit_parents
		where unit_id = @a_unit_id
		and parent_unit_id = @a_parent_unit_id)
    begin
        raiserror('Specified parent unit was already added (unit_id = %s, parent_unit_id = %s) (#error_code = 20050)',
        16, 1, @a_unit_id_str, @a_parent_unit_id_str);
        return;
    end;
           
    --Checking that there will be no cycle
    if exists(select 1 from dbo.p_get_unit_parents(@a_parent_unit_id)
		where unit_id = @a_unit_id)
    begin
		raiserror('Building a cycle of units is forbidden (#error_code = 20033)', 16, 1);
		return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_unit_parents (unit_id, parent_unit_id)
    values (@a_unit_id, @a_parent_unit_id);
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Getting affected new users
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    --Refreshing new users acl
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;		
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_unit_parent';

go

--Function of deleting unit parent
create procedure meta_delete_unit_parent(
    --Unit id
    @a_unit_id binary(16),
    --Parent unit id
    @a_parent_unit_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
    --Result
    @a_result numeric,
    --Unit id str
    @a_unit_id_str varchar(32),
    --Parent unit id str
    @a_parent_unit_id_str varchar(32);
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_parent_unit_id, @a_result output;
    
    if @a_result = 0 return;
    
    set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
    set @a_parent_unit_id_str = dbo.p_bin_to_str(@a_parent_unit_id);
    
    --Searching for the unit parent
    if not exists(select 1 from meta_unit_parents
		where unit_id = @a_unit_id
		and parent_unit_id = @a_parent_unit_id)
    begin
        raiserror('Specified parent unit was not found (unit_id = %s, parent_unit_id = %s) (#error_code = 20051)',
        16, 1, @a_unit_id_str, @a_parent_unit_id_str);
        return;
    end;
    
    --Getting affected old users
    select distinct user_name into #a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from p_get_unit_parents_children(@a_unit_id));
    
    set xact_abort on;
    begin tran;
        
    --Updating the metadata
    delete from meta_unit_parents
    where unit_id = @a_unit_id
    and parent_unit_id = @a_parent_unit_id;
    
    declare
    --Affected user name
    @a_affected_user_name varchar(26),
    --Affected user cursor
    @a_affected_user_cursor cursor;
    
    --Refreshing old users acl
    set @a_affected_user_cursor = cursor fast_forward read_only for
    select user_name from #a_affected_users;
    
    open @a_affected_user_cursor;
    fetch next from @a_affected_user_cursor into @a_affected_user_name;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_refresh_user_acl @a_affected_user_name;    
		fetch next from @a_affected_user_cursor into @a_affected_user_name;
    end;
    
    close @a_affected_user_cursor;
    deallocate @a_affected_user_cursor;
    
    commit;
end;

go